from flask import Flask, render_template, jsonify, request
import pandas as pd
from openpyxl import load_workbook
import pathlib
import mysql.connector
from mysql.connector import pooling, Error

username = 'liezhisheng'
connection_pool = mysql.connector.pooling.MySQLConnectionPool(
    pool_name = "mypool",
    pool_size = 5,
# conn = mysql.connector.connect(
    host="",  # 主机名或 IP 地址
    port="",
    user="",  # 用户名
    password="",  # 密码
    database=""  # 数据库名称
)
# cursor = conn.cursor()

app = Flask(__name__)
colors = ["#900021", "#FBD26A", "#8F4B28", "#01847F", "#40E0D0", "#002FA7", "#81D8CF", "#003152", "#B05923", "#E60000"]
group_info = {}
new_data = {}

@app.route('/')
def index():
    return render_template('index.html')

@app.route('/get_options', methods=['GET'])
def get_options():
    sheetnames = []
    try:
        sql = "select systems from zn_user where username='" + username + "'"
        conn = connection_pool.get_connection()
        if conn.is_connected():
            cursor = conn.cursor()
            cursor.execute(sql)
            rows = cursor.fetchall()[0][0]
            sheetnames = rows.split(',')
    except Error as e:
        print(e)
    finally:
        if conn.is_connected():
            cursor.close()
            conn.close()
    data_sources = []
    for sheetname in sheetnames:
        data_sources.append({"label": sheetname, "value": sheetname})
    print(data_sources)
    return jsonify({"options": data_sources})


@app.route('/topology')
def topology():
    topology_data = new_data
    new_data["group_info"] = group_info
    print(jsonify(topology_data))
    return jsonify(topology_data)

@app.route('/load_data', methods=['POST'])
def load_data():
    global new_data
    global group_info
    data = request.get_json()
    sheetname = data.get('sheetname')
    group_info = {}
    new_data={"nodes": [],"links": []}
    items = []
    try:
        conn = connection_pool.get_connection()
        if conn.is_connected():
            cursor = conn.cursor()
            first_sql = "select info from zn_sys_info where sysname='" + sheetname + "'"
            cursor.execute(first_sql)
            item = cursor.fetchall()[0][0]
            if item is not None:
                ones = item.split(",")
                for one in ones:
                    ind, name = one.split(":")
                    ind = int(ind)
                    group_info[ind] = {"name": name, "color": colors[ind-1]}

            info_sql = "SELECT id,group_id,ip,comment,connection FROM smim.zn_info where sysname='" + sheetname + "' order by id;"
            cursor.execute(info_sql)
            items = cursor.fetchall()
    except Error as e:
        print(e)
    finally:
        if conn.is_connected():
            cursor.close()
            conn.close()
        
    for row in items:
        id=row[0]
        group=row[1]
        ip = row[2]
        card = row[3]
        if id is None or group is None:
            return jsonify("fail", "id or group is wrong")
        id = str(id)
        if ip is None:
            ip = ""
        if card is None:
            card = ""
        node = {"id": id, "group": int(group), "ip": ip, "card": card}
        new_data["nodes"].append(node)
        if row[4] is not None and row[4] != "":
            conns = str(row[4]).split(',')
            for conn in conns:
                link =  {"source": id, "target": conn, "value": 1}
                new_data["links"].append(link)

    print("success change")
    print(new_data)
    return jsonify({'success': True})


if __name__ == '__main__':
    app.run(debug=True)
